/***********************************************************************************************************************************
 *    Name: DBAdmin_Core.sql
 *  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Core Database Administration Tools
 *
 * Toolbar Snippits
 *
 * Ctrl+F1
 * EXECUTE master.DBAdmin.GetServerInfo;
 *
 * Ctrl+3
 * SELECT * FROM master.DBAdmin.ShowBackupsStatus ORDER BY COALESCE(tlog_age_mi/60.0, diff_age_hh, full_age_hh) DESC, database_name;
 *
 * Ctrl+4
 * SELECT * FROM master.DBAdmin.ShowProcessInfo WHERE 50 < spid ORDER BY chain DESC, spid, ecid;
 *
 * Ctrl+5
 * SELECT * FROM master.DBAdmin.ShowBufferPool ORDER BY [Buffer Pool (MB)] DESC;

 * Ctrl+6
 * SELECT [Trace ID], [Property Name], [Property Value] FROM master.DBAdmin.ShowTraceInto ORDER BY [Trace ID], Property;
**/

--================================================================================================================================--
-- Server Configuration
SET NOCOUNT ON;
PRINT N'Server Configuration...';
USE master;
EXECUTE dbo.sp_configure N'show advanced options', 1; RECONFIGURE WITH OVERRIDE;
EXECUTE dbo.sp_configure N'Agent XPs', 1;
EXECUTE dbo.sp_configure N'cost threshold for parallelism', 20;
EXECUTE dbo.sp_configure N'Database Mail XPs', 1;
EXECUTE dbo.sp_configure N'remote admin connections', 1;
BEGIN TRY EXECUTE dbo.sp_configure N'backup compression default', 1; END TRY BEGIN CATCH END CATCH;
RECONFIGURE;
GO


USE msdb;
PRINT N'msdb...';
BEGIN TRY CREATE INDEX media_set_id       ON dbo.backupset (media_set_id);              END TRY BEGIN CATCH END CATCH;
BEGIN TRY CREATE INDEX restore_history_id ON dbo.restorefile (restore_history_id);      END TRY BEGIN CATCH END CATCH;
BEGIN TRY CREATE INDEX restore_history_id ON dbo.restorefilegroup (restore_history_id); END TRY BEGIN CATCH END CATCH;

PRINT N'SQL Agent Properties...';
EXECUTE dbo.sp_set_sqlagent_properties
	@jobhistory_max_rows		= -1,	-- maximum job history log size (in rows)
	@jobhistory_max_rows_per_job= -1,	-- maximum job history rows per job
	@sqlserver_restart			=  1,	-- auto restart SQL Server if it stops unexpectedly
	@monitor_autostart			=  1;	-- auto restart SQL Server Agent if it stops unexpectedly
BEGIN TRY;
	EXECUTE ('UPDATE dbo.syspolicy_configuration_internal SET current_value= 91 WHERE name = N''HistoryRetentionInDays'';');	-- 13 Weeks
END TRY BEGIN CATCH END CATCH;

IF NOT EXISTS (SELECT * FROM dbo.syscategories WHERE name = N'DBAdmin' AND category_class = 1) EXECUTE dbo.sp_add_category @class= N'JOB'     , @type= N'LOCAL', @name= N'DBAdmin';
IF NOT EXISTS (SELECT * FROM dbo.syscategories WHERE name = N'DBAdmin' AND category_class = 2) EXECUTE dbo.sp_add_category @class= N'ALERT'   , @type= N'NONE' , @name= N'DBAdmin';
IF NOT EXISTS (SELECT * FROM dbo.syscategories WHERE name = N'DBAdmin' AND category_class = 3) EXECUTE dbo.sp_add_category @class= N'OPERATOR', @type= N'NONE' , @name= N'DBAdmin';
USE master;
GO


--================================================================================================================================--
PRINT N'Schema...';
IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'DBAdmin') BEGIN
	-- Delete all existing DBAdmin objects.
	DECLARE @tsql_command NVARCHAR(MAX); SET @tsql_command= N'';
	SELECT @tsql_command= @tsql_command + N'DROP ' + CASE type WHEN 'P' THEN 'PROCEDURE [' WHEN 'V' THEN 'VIEW [' ELSE 'FUNCTION [' END + SCHEMA_NAME(schema_id) + '].[' + name + ']; '
	  FROM sys.objects WHERE schema_id = SCHEMA_ID('DBAdmin') AND type IN ('P','V','FN','IF','TF');
	EXECUTE (@tsql_command);
	BEGIN TRY DROP ROLE DBAdmin; END TRY BEGIN CATCH END CATCH;
END; ELSE EXECUTE ('CREATE SCHEMA DBAdmin AUTHORIZATION dbo;');	-- Create the DBAdmin schema if it doesn't already exist.

BEGIN TRY CREATE ROLE DBAdmin AUTHORIZATION dbo; END TRY BEGIN CATCH END CATCH;
EXECUTE dbo.sp_addrolemember N'db_datareader', N'DBAdmin';
EXECUTE dbo.sp_addrolemember N'db_denydatawriter', N'DBAdmin';
GRANT EXECUTE ON SCHEMA::DBAdmin TO DBAdmin;
PRINT N'Procedures/Fumctions/Views...';
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Return the SQL Server version number as a NUMERIC.
CREATE FUNCTION DBAdmin.ProductVersion() RETURNS FLOAT AS BEGIN RETURN PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(16)),4) + '.' + PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(16)),3); END;
GO

--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Convert a comma-seperated list to a single-column table.
-- http://sqlindia.com/split-function-sql-server-performance-best-practise/
-- http://www.sqlservercentral.com/articles/Tally+Table/72993/
-- https://www.simple-talk.com/blogs/2012/01/05/using-xml-to-pass-lists-as-parameters-in-sql-server/
CREATE FUNCTION DBAdmin.List (@s NVARCHAR(MAX)) RETURNS @list TABLE (item NVARCHAR(255) NOT Null) AS BEGIN;
DECLARE @x XML; SET @x= CAST('<E>' + REPLACE(@s,',','</E><E>') + '</E>' AS XML);
  INSERT INTO @list SELECT e.value('.', 'NVARCHAR(128)') AS item FROM @x.nodes('/E') AS x(e);
  RETURN;
END;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Converts a comma-seperated list of database names to a table of the database IDs and other properties. Only returns
--			online DBs. Supports the following special identifiers:
--				* - All Databases
--				$ - System Databases
--				@ - User Databases
--			Preface a database name with "-" (minus) to exclude it.
CREATE FUNCTION DBAdmin.DBList (@db NVARCHAR(MAX)= N'*') RETURNS @dblist TABLE (database_id INT NOT Null PRIMARY KEY CLUSTERED, database_name NVARCHAR(128) NOT Null, is_read_only BIT NOT Null, recovery_model TINYINT NOT Null, source_database_id INT Null) AS BEGIN;
DECLARE @include TABLE (item NVARCHAR(128) NOT Null);
DECLARE @exclude TABLE (item NVARCHAR(128) NOT Null);

  INSERT INTO @include (item) SELECT DISTINCT item FROM DBAdmin.List(@db);

  DELETE FROM @include WHERE item = N'*';	-- All Databases
  IF 0 < @@ROWCOUNT INSERT INTO @include SELECT name FROM sys.databases;

  DELETE FROM @include WHERE item = N'$';	-- System Databases
  IF 0 < @@ROWCOUNT INSERT INTO @include SELECT name FROM sys.databases WHERE database_id IN (1,3,4) OR is_distributor = 1;

  DELETE FROM @include WHERE item = N'@';	-- User Databases
  IF 0 < @@ROWCOUNT INSERT INTO @include SELECT name FROM sys.databases WHERE 4 < database_id AND is_distributor = 0;

  INSERT INTO @exclude (item) SELECT SUBSTRING(item, 2, LEN(item)-1) FROM @include WHERE item LIKE N'-%';
  IF 0 < @@ROWCOUNT BEGIN
	DELETE FROM @include WHERE item LIKE N'-%';
	DELETE FROM @exclude WHERE item IN (N'-*', N'-$', N'-@');
  END;

  INSERT INTO @dblist SELECT DISTINCT database_id, name, is_read_only, recovery_model, source_database_id
	FROM @include INNER JOIN sys.databases ON (item = name)
	WHERE state = 0  AND database_id <> 2 AND name NOT IN (SELECT item FROM @exclude);
RETURN;
END;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Return the instance pathname for the specified @item. Valid items are:
--		@Item Value			Description
--		SQLDataRoot			Instance Installation Directory
--		DefaultData			MDFs Go Here
--		DefaultLog			LDFs Go Here
--		BackupDirectory		BAKs Go Here
--		ErrorDumpDir		SQL Server Error Log Files
--		WorkingDirectory	SQL Agent Log and Default for Job Output
CREATE FUNCTION DBAdmin.GetInstancePath (@Item NVARCHAR(255)) RETURNS NVARCHAR(255) AS BEGIN;
DECLARE
  @Key		NVARCHAR(255),
  @Value	NVARCHAR(255);

IF @Item IN (N'DefaultData', N'DefaultLog', N'BackupDirectory', N'SQLDataRoot', N'ErrorDumpDir', N'WorkingDirectory') BEGIN;
  SET @Key= CASE @Item
	WHEN N'SQLDataRoot'			THEN N'Software\Microsoft\MSSQLServer\Setup'
	WHEN N'ErrorDumpDir'		THEN N'Software\Microsoft\MSSQLServer\CPE'
	WHEN N'WorkingDirectory'	THEN N'Software\Microsoft\MSSQLServer\SQLServerAgent'
	ELSE N'Software\Microsoft\MSSQLServer\MSSQLServer' END;
  EXECUTE dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @Key, @Item, @Value OUTPUT;

  IF @Value IS Null BEGIN;
	IF @Item = N'DefaultData' SELECT @Value= SUBSTRING(physical_name, 1, LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1) FROM sys.database_files WHERE file_id = 1;
	IF @Item = N'DefaultLog'  SELECT @Value= SUBSTRING(physical_name, 1, LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1) FROM sys.database_files WHERE file_id = 2;
  END;

  IF RIGHT(@Value,1) <> N'\' SET @Value= @Value+ N'\';
END;

RETURN @Value;
END;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Return a rowset containing some useful information about the server.
CREATE PROCEDURE DBAdmin.GetServerInfo AS;
DECLARE @ServerInfo TABLE (RowID TINYINT NOT Null IDENTITY(1,1) PRIMARY KEY CLUSTERED, Name NVARCHAR(128) NOT Null, Value SQL_VARIANT Null);
DECLARE @xp_msver TABLE ([Index] TINYINT NOT Null PRIMARY KEY CLUSTERED, Name NVARCHAR(128) NOT Null, Internal_Value INTEGER Null, Character_Value NVARCHAR(128) Null);
DECLARE @sp_configure TABLE (name NVARCHAR(128) NOT Null, minimum INT NOT Null, maximum INT NOT Null, config_value INT NOT Null, run_value INT NOT Null);
SET NOCOUNT ON;

INSERT INTO @ServerInfo (Name, Value) VALUES ('Server Name'		, @@SERVERNAME);
INSERT INTO @ServerInfo (Name, Value) VALUES ('Network Name'	, SERVERPROPERTY('ServerName'));
INSERT INTO @ServerInfo (Name, Value) VALUES ('NetBIOS Name'	, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'));
INSERT INTO @ServerInfo (Name, Value) VALUES ('Is Clustered'	, SERVERPROPERTY('IsClustered'));
INSERT INTO @ServerInfo (Name, Value) VALUES ('Product Version'	, SERVERPROPERTY('ProductVersion'));
INSERT INTO @ServerInfo (Name, Value) VALUES ('Edition'			, SERVERPROPERTY('Edition'));

INSERT INTO @xp_msver EXECUTE sys.xp_msver;
INSERT INTO @ServerInfo (Name, Value) SELECT Name, Internal_Value FROM @xp_msver WHERE [Index] IN (16,19);

INSERT INTO @ServerInfo (Name, Value) SELECT
	Name	= 'Instance Installation Date',
	Value	= CAST(create_date AS smalldatetime)
  FROM sys.server_principals WHERE sid = 0x010100000000000512000000;

INSERT INTO @ServerInfo (Name, Value) SELECT
	Name	= 'OS Boot Date',
	Value	= CAST(DATEADD(ss, -(ms_ticks/1000), GETDATE()) AS SMALLDATETIME)
  FROM sys.dm_os_sys_info;

INSERT INTO @ServerInfo (Name, Value) SELECT
	Name	= 'Instance Startup Date',
	Value	= CAST(create_date AS smalldatetime)
  FROM sys.databases WHERE name = N'tempdb';

INSERT INTO @sp_configure EXECUTE sys.sp_configure;
INSERT INTO @ServerInfo (Name, Value) SELECT name, run_value FROM @sp_configure
  WHERE name IN ('cost threshold for parallelism','max degree of parallelism','max server memory (MB)');

INSERT INTO @ServerInfo (Name, Value) VALUES ('SQL Data Root',				DBAdmin.GetInstancePath('SQLDataRoot'));
INSERT INTO @ServerInfo (Name, Value) VALUES ('Agent Working Directory',	DBAdmin.GetInstancePath('WorkingDirectory'));
INSERT INTO @ServerInfo (Name, Value) VALUES ('Error Dump Dir',				DBAdmin.GetInstancePath('ErrorDumpDir'));
INSERT INTO @ServerInfo (Name, Value) VALUES ('Default Data',				DBAdmin.GetInstancePath('DefaultData'));
INSERT INTO @ServerInfo (Name, Value) VALUES ('Default Log',				DBAdmin.GetInstancePath('DefaultLog'));
INSERT INTO @ServerInfo (Name, Value) VALUES ('Backup Directory',			DBAdmin.GetInstancePath('BackupDirectory'));

SELECT Name, Value FROM @ServerInfo ORDER BY RowID;
SET NOCOUNT OFF;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Return a single row containing some useful information about the server.
CREATE PROCEDURE DBAdmin.GetConfiguration AS
SET NOCOUNT ON;
DECLARE @sp_configure TABLE (name NVARCHAR(128) NOT Null, minimum INT NOT Null, maximum INT NOT Null, config_value INT NOT Null, run_value INT NOT Null);
BEGIN TRY INSERT INTO @sp_configure EXECUTE sp_configure N'max server memory (MB)'; END TRY
BEGIN CATCH INSERT INTO @sp_configure (name, minimum, maximum, config_value, run_value) VALUES (N'max server memory (MB)', -1, -1, -1, -1); END CATCH;
SELECT
	ServerName		= @@SERVERNAME,
	HostName		= SUBSTRING(@@SERVERNAME, 1, LEN(@@SERVERNAME)-CHARINDEX(N'\',REVERSE(@@SERVERNAME))),
	IPAddress		= (SELECT local_net_address + N':' + CAST(local_tcp_port AS NCHAR(5)) FROM sys.dm_exec_connections WHERE session_id = @@SPID),
	IsClustered		= CAST(SERVERPROPERTY('IsClustered') AS NVARCHAR(128)),
	ProductVersion	= CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)),
	Edition			= CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128)),
	MaxMemory		= c.run_value,
	DefaultData		= DBAdmin.GetInstancePath('DefaultData'),
	DefaultLog		= DBAdmin.GetInstancePath('DefaultLog'),
	BackupDirectory	= DBAdmin.GetInstancePath('BackupDirectory'),
	SQLDataRoot		= DBAdmin.GetInstancePath('SQLDataRoot'),
	ErrorDumpDir	= DBAdmin.GetInstancePath('ErrorDumpDir'),
	WorkingDirectory= DBAdmin.GetInstancePath('WorkingDirectory')
  FROM @sp_configure c;
SET NOCOUNT OFF;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Show system activity with blocking chain.
CREATE VIEW DBAdmin.ShowProcessInfo AS
WITH
  t (spid, text) AS (SELECT spid, text FROM sys.sysprocesses CROSS APPLY sys.dm_exec_sql_text(sql_handle)),
  b (session_id, blocking_session_id) AS (SELECT session_id, blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0),
  h (chain, session_id, blocking_session_id) AS (
		SELECT           CAST(                        session_id AS                VARCHAR(128)),   session_id, CAST(0 AS SMALLINT)   FROM sys.dm_exec_sessions WHERE session_id IN (SELECT blocking_session_id FROM b) AND session_id NOT IN (SELECT session_id FROM b)
		UNION ALL SELECT CAST(h.chain + N'-' + CAST(r.session_id AS VARCHAR(5)) AS VARCHAR(128)), r.session_id, r.blocking_session_id FROM sys.dm_exec_requests r INNER JOIN h ON (r.blocking_session_id = h.session_id) WHERE r.blocking_session_id <> 0)
  SELECT
		h.chain,
		s.spid,
		s.ecid,
		s.kpid,
		s.blocked,
		s.waittype,
		s.waittime,
		lastwaittype	= RTRIM(s.lastwaittype),
		waitresource	= RTRIM(s.waitresource),
		database_name	= DB_NAME(s.dbid),
--		s.uid,
		s.cpu,
		s.physical_io,
		s.memusage,
		s.login_time,
		s.last_batch,
		s.open_tran,
		status			= RTRIM(s.status),
--		s.sid,
		hostname		= RTRIM(s.hostname),
		program_name	= RTRIM(s.program_name),
		hostprocess		= RTRIM(s.hostprocess),
		nt_domain		= RTRIM(s.nt_domain),
		nt_username		= RTRIM(s.nt_username),
		s.net_address,
		net_library		= RTRIM(s.net_library),
		loginame		= RTRIM(s.loginame),
--		s.context_info,
--		s.sql_handle,
		s.stmt_start,
		s.stmt_end,
		s.request_id,
		cmd				= RTRIM(s.cmd),
		t.text
	FROM sys.sysprocesses s LEFT JOIN h ON (s.spid = h.session_id) LEFT JOIN t ON (s.spid = t.spid);
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Return a rowset showing the hours since the latest database backup for all DBs.
CREATE VIEW DBAdmin.ShowBackupsStatus AS WITH
  d (database_name, recovery_model) AS (SELECT name, recovery_model_desc FROM master.sys.databases /*UNION ALL SELECT DISTINCT database_name, recovery_model COLLATE SQL_Latin1_General_CP1_CI_AS FROM msdb.dbo.backupset*/),
  x (database_name, backup_type, backup_finish_date) AS (SELECT database_name, type, MAX(backup_finish_date) FROM msdb.dbo.backupset WHERE is_copy_only = 0 GROUP BY database_name, type)
SELECT
	d.database_name,
	d.recovery_model,
	full_age_hh= (SELECT DATEDIFF(hh, backup_finish_date, CURRENT_TIMESTAMP) FROM x WHERE x.database_name = d.database_name AND x.backup_type = 'D'),
	diff_age_hh= (SELECT DATEDIFF(hh, backup_finish_date, CURRENT_TIMESTAMP) FROM x WHERE x.database_name = d.database_name AND x.backup_type = 'I' AND x.backup_finish_date > (SELECT     backup_finish_date  FROM x WHERE x.database_name = d.database_name AND x.backup_type  =  'D'     )),
	tlog_age_mi= (SELECT DATEDIFF(mi, backup_finish_date, CURRENT_TIMESTAMP) FROM x WHERE x.database_name = d.database_name AND x.backup_type = 'L' AND x.backup_finish_date > (SELECT MAX(backup_finish_date) FROM x WHERE x.database_name = d.database_name AND x.backup_type IN ('D','I')))
  FROM d
  WHERE d.database_name <> N'TempDB';
GO

--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:
CREATE FUNCTION	DBAdmin.ShowBackupsAge(@BackupTypes NCHAR(3)= N'DI') RETURNS TABLE AS RETURN
WITH b (database_name, backup_type, backup_finish_date) AS (
	SELECT DISTINCT database_name, type, MAX(backup_finish_date)
	  FROM msdb.dbo.backupset WHERE is_copy_only = 0 AND 0 < CHARINDEX(type, @BackupTypes) GROUP BY database_name, type)
SELECT
	b.database_name,
	b.backup_finish_date,
	b.backup_type,
	backup_age= DATEDIFF(hh, b.backup_finish_date, CURRENT_TIMESTAMP),
	m.physical_device_name,
	m.device_type
  FROM b INNER JOIN msdb.dbo.backupset b0 ON (b.database_name = b0.database_name AND b.backup_finish_date = b0.backup_finish_date AND b.backup_type = b0.type)
	INNER JOIN msdb.dbo.backupmediafamily m ON (b0.media_set_id = m.media_set_id)
  WHERE b0.is_copy_only = 0;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Return information about current server-side traces
CREATE VIEW DBAdmin.ShowTraceInto AS SELECT
	[Trace ID]		= traceid,
	[Property Name]	= CASE property WHEN 1 THEN N'Options' WHEN 2 THEN N'File Name' WHEN 3 THEN N'Max Size' WHEN 4 THEN N'Stop Time' WHEN 5 THEN N'Status' END,
	[Property Value]= CASE WHEN property=5 AND value=1 THEN N'Running' WHEN property=5 AND value=0 THEN N'Stopped'ELSE value END,
	Property		-- only here for sorting by the numeric value
  FROM dbo.fn_trace_getinfo(DEFAULT);
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Return a string in the format "[@prefix] YYYY-MM-DD HH-MM-SS @suffix"
CREATE FUNCTION DBAdmin.NameBackupFile(@prefix NVARCHAR(128), @suffix NVARCHAR(128)) RETURNS NVARCHAR(255) AS BEGIN;
RETURN QUOTENAME(@prefix) + SPACE(1) + REPLACE(CONVERT(NVARCHAR(19),CURRENT_TIMESTAMP,120), N':', N'-') + SPACE(1) + @suffix;
END;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Calculate the total, used, and changed (since last full backup) extents in the database.
CREATE PROCEDURE DBAdmin.GetDBExtents (@db_name NVARCHAR(128), @Total INT OUTPUT, @Used INT OUTPUT, @Changed INT OUTPUT) AS
SET NOCOUNT ON;
CREATE TABLE #ShowFileStats (
	FileID			INT				NOT Null,
	FileGroup		INT				NOT Null,
	TotalExtents	BIGINT			NOT Null,
	UsedExtents		BIGINT			NOT Null,
	LogicalFileName	NVARCHAR(128)	NOT Null,	
	FilePath		VARCHAR(260)	NOT Null);
CREATE TABLE #Page (
	ParentObject	NVARCHAR(128)			NOT Null,
	Object			NVARCHAR(128)			NOT Null,
	Field			NVARCHAR(128)			NOT Null,
	Value			NVARCHAR(128)			NOT Null);
DECLARE FileStats CURSOR LOCAL FAST_FORWARD FOR SELECT FileID, TotalExtents FROM #ShowFileStats;
DECLARE Extents CURSOR LOCAL FAST_FORWARD FOR SELECT Field FROM #Page WHERE Object LIKE 'DIFF_MAP: Extent Alloc Status @%' AND Value = '    CHANGED';
DECLARE
  @tsql_command	NVARCHAR(MAX),
  @FileID		INT,
  @TotalExtents	BIGINT,
  @UsedExtents	BIGINT,
  @CurrentDCM	BIGINT,
  @field		NVARCHAR(128),
  @ChangedPages	BIGINT,
  @dash TINYINT, @a1 TINYINT, @a2 TINYINT, @b1 TINYINT, @b2 TINYINT,
  @start BIGINT, @end BIGINT;

SET @tsql_command= REPLACE(N'USE [?]; DBCC SHOWFILESTATS WITH TABLERESULTS, NO_INFOMSGS;', N'?', @db_name);
SET @ChangedPages= 0;
INSERT INTO #ShowFileStats EXECUTE (@tsql_command);
OPEN FileStats;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM FileStats INTO @FileID, @TotalExtents;
  IF @@FETCH_STATUS <> 0 BREAK;
  SET @CurrentDCM= 6;
  WHILE @CurrentDCM <= @TotalExtents*8 BEGIN;
	INSERT INTO #Page EXECUTE sys.sp_executesql N'DBCC PAGE(@dbname,@FileID,@CurrentDCM,3) WITH TABLERESULTS, NO_INFOMSGS', N'@dbname NVARCHAR(128), @FileID INT, @CurrentDCM BIGINT', @dbname= @db_name, @FileID= @FileID, @CurrentDCM= @CurrentDCM;
	SET @CurrentDCM= @CurrentDCM + 511232;	-- yes it's a magic number
  END;
END;
CLOSE FileStats;
DEALLOCATE FileStats;

OPEN Extents;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM Extents INTO @field;
  IF @@FETCH_STATUS <> 0 BREAK;
  SET @dash= CHARINDEX(N'-',@field);
  SELECT
	@a1= CHARINDEX(N':', @field, 1    )+1,
	@a2= CHARINDEX(N')', @field, 1    ),
	@b1= CHARINDEX(N':', @field, @dash)+1,
	@b2= CHARINDEX(N')', @field, @dash);
	SET          @start= SUBSTRING(@field, @a1, @a2-@a1);
	IF 0 < @b2 SET @end= SUBSTRING(@field, @b1, @b2-@b1); ELSE SET @end= @start;
	SET @ChangedPages= @ChangedPages + (@end - @start + 8);
END;
CLOSE Extents;
DEALLOCATE Extents;

SELECT
	@Total	= SUM(TotalExtents),
	@Used	= SUM(UsedExtents),
	@Changed= (@ChangedPages/8)
  FROM #ShowFileStats;
SET NOCOUNT OFF;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Back up database(s). Assume a Full backup. Condense to a Differential backup if conditions are met
CREATE PROCEDURE DBAdmin.BackupDBs (
	@db					NVARCHAR(MAX)	= N'*',	-- database(s) to back up
	@hrs_limit			SMALLINT		= Null,	-- allow a differential backup if the age of the latest full backup is less than @hrs_limit
	@pct_limit			TINYINT			= Null,	-- allow a differential backup if less than @pct_limit database extents are changed
	@force_differential	BIT				= 0,	-- force a differential backup. overrides @hrs_limit and @pct_limit
	@whatif				BIT				= 0		-- if 1, don't actually perform any backups
		) AS;
SET NOCOUNT ON;
DECLARE
  @backupName		NVARCHAR(128),
  @backupDirectory	NVARCHAR(255),
  @backupDevice		NVARCHAR(255),
  @deviceType		TINYINT,
  @tsql				NVARCHAR(MAX),
  @dbid				INTEGER,
  @db_name			NVARCHAR(128),
  @backup_age		INTEGER,
  @total			INTEGER,
  @used				INTEGER,
  @changed			INTEGER,
  @type				CHAR(8),
  @ok_differential	BIT,
  @errors			SMALLINT,
  @returnValue		INTEGER;

IF OBJECT_ID('TempDB..#backupHeader') IS NOT Null DROP TABLE #backupHeader;
CREATE TABLE #backupHeader (
	BackupName				NVARCHAR(128),
	BackupDescription		NVARCHAR(255),
	BackupType				SMALLINT,
	ExpirationDate			DATETIME,
	Compressed				BIT,
	Position				SMALLINT,
	DeviceType				TINYINT,
	UserName				NVARCHAR(128),
	ServerName				NVARCHAR(128),
	DatabaseName			NVARCHAR(128),
	DatabaseVersion			INT,
	DatabaseCreationDate	DATETIME,
	BackupSize				NUMERIC(20,0),
	FirstLSN				NUMERIC(25,0),
	LastLSN					NUMERIC(25,0),
	CheckpoINTLSN			NUMERIC(25,0),
	DatabaseBackupLSN		NUMERIC(25,0),
	BackupStartDate			DATETIME,
	BackupFinishDate		DATETIME,
	SortOrder				SMALLINT,
	CodePage				SMALLINT,
	UnicodeLocaleId			INT,
	UnicodeComparisonStyle	INT,
	CompatibilityLevel		TINYINT,
	SoftwareVendorId		INT,
	SoftwareVersionMajor	INT,
	SoftwareVersionMinor	INT,
	SoftwareVersionBuild	INT,
	MachineName				NVARCHAR(128),
	Flags 					INT,
	BindingID				UNIQUEIDENTIFIER,
	RecoveryForkID			UNIQUEIDENTIFIER,
	Collation				NVARCHAR(128),
	FamilyGUID				UNIQUEIDENTIFIER,
	HasBulkLoggedData		BIT,
	IsSnapshot				BIT,
	IsReadOnly				BIT,
	IsSingleUser			BIT,
	HasBackupChecksums		BIT,
	IsDamaged				BIT,
	BeginsLogChain			BIT,
	HasIncompleteMetaData	BIT,
	IsForceOffline			BIT,
	IsCopyOnly				BIT,
	FirstRecoveryForkID		UNIQUEIDENTIFIER,
	ForkPoINTLSN			NUMERIC(25,0) Null,
	RecoveryModel			NVARCHAR(60),
	DifferentialBaseLSN		NUMERIC(25,0) Null,
	DifferentialBaseGUID	UNIQUEIDENTIFIER,
	BackupTypeDescription	NVARCHAR(60),
	BackupSetGUID			UNIQUEIDENTIFIER Null,
	CompressedBackupSize	BIGINT,					-- 10+
	Containment				TINYINT,				-- 11+
	KeyAlgorithm			NVARCHAR(32),			-- 12+
	EncryptorThumbprint		VARBINARY(20),			-- 12+
	EncryptorType			NVARCHAR(32)			-- 12+
);
IF DBAdmin.ProductVersion() < 12 ALTER TABLE #backupHeader DROP COLUMN EncryptorType, EncryptorThumbprint, KeyAlgorithm;
IF DBAdmin.ProductVersion() < 11 ALTER TABLE #backupHeader DROP COLUMN Containment;
IF DBAdmin.ProductVersion() < 10 ALTER TABLE #backupHeader DROP COLUMN CompressedBackupSize;

SET @backupDirectory= DBAdmin.GetInstancePath('BackupDirectory');
SET @errors= 0;
IF @hrs_limit < 0 SET @hrs_limit= Null;
IF @pct_limit < 0 SET @pct_limit= Null;
IF @force_differential = 1 BEGIN SET @hrs_limit= Null; SET @pct_limit= Null; END;

PRINT @backupDirectory;
DECLARE list CURSOR LOCAL FAST_FORWARD FOR
	SELECT dbl.database_id, dbl.database_name, ba.backup_age, ba.physical_device_name, ba.device_type
	  FROM DBAdmin.DBList(@db) dbl LEFT OUTER JOIN DBAdmin.ShowBackupsAge('D') ba ON (dbl.database_name = ba.database_name)
	  WHERE source_database_id IS Null ORDER BY database_id DESC;
OPEN list;
WHILE (0=0) BEGIN;

  FETCH NEXT FROM list INTO @dbid, @db_name, @backup_age, @backupDevice, @deviceType;
  IF @@FETCH_STATUS <> 0 BREAK;
  PRINT CHAR(13)+REPLICATE('=', 132);
  PRINT CONVERT(NVARCHAR(19), CURRENT_TIMESTAMP, 120) + SPACE(1) + CAST(@dbid AS NVARCHAR(9)) + SPACE(1) + @db_name;

  -- test eligability for differential backup
  IF @dbid <> 1 AND (@hrs_limit IS NOT Null OR @pct_limit IS NOT Null) SET @ok_differential= 1; ELSE BEGIN
	SET @ok_differential= 0;
	IF @force_differential = 0 PRINT N'Taking a Full Backup';
  END;

  -- does the most recent full backup exist ON DISK?
  IF (@ok_differential = 1 AND  @backupDevice IS NOT Null) BEGIN
	PRINT N'Checking for Full Backup: ' + @backupDevice;
	BEGIN TRY;
	  INSERT INTO #backupHeader EXECUTE sys.sp_executesql N'RESTORE HEADERONLY FROM DISK= @backupDevice', N'@backupDevice NVARCHAR(255)', @backupDevice;
	  PRINT N' + PASSED!';
	END TRY BEGIN CATCH
	  SET @ok_differential= 0;
	  PRINT N' - Failed';
	END CATCH;
  END;

  -- is the age of the most recent full backup less than @hrs_limit?
  IF @ok_differential = 1 AND @hrs_limit IS NOT Null BEGIN
	PRINT N'Testing Age Threshold: ' + CAST(@backup_age AS NVARCHAR(5)) + N' < ' + CAST(@hrs_limit AS NVARCHAR(5));
	IF @backup_age < @hrs_limit PRINT N' + PASSED!'; ELSE BEGIN
	  SET @ok_differential= 0;
	  PRINT N' - Failed';
	END;
  END;

  -- are the database changes less than @pct_limit?
  IF @ok_differential = 1 AND @pct_limit IS NOT Null BEGIN
	EXECUTE DBAdmin.GetDBExtents @db_name, @total OUTPUT, @used OUTPUT, @changed OUTPUT;
	PRINT N'Testing Change Threshold: ' + CAST(100*@changed/@used AS NVARCHAR(5)) + N' < ' + CAST(@pct_limit AS NVARCHAR(5));
	IF 100*@changed/@used < @pct_limit PRINT N' + PASSED!';
	ELSE BEGIN
	  SET @ok_differential= 0;
	  PRINT N' - Failed';
	END;
  END;

  -- override?
  IF @force_differential = 1 SET @ok_differential= 1;

  -- build the statement
  SET @tsql= N'BACKUP DATABASE [' + @db_name + N'] TO DISK= @disk WITH NAME= @name, DESCRIPTION= @description';
  IF @ok_differential = 1 BEGIN
	SET @tsql= @tsql + N', DIFFERENTIAL;';
	SET @type= 'DIFF.bak';
  END; ELSE BEGIN
	SET @tsql= @tsql + N';';
	SET @type= 'FULL.bak';
  END;
  SET @backupName= DBAdmin.NameBackupFile(@db_name, @type);
  SET @backupDevice= @backupDirectory + @backupName;

  -- ...and do it
  PRINT N'Backing up to: ' + @backupDevice;
  BEGIN TRY;
	IF @whatif = 0 EXECUTE @returnValue= sys.sp_executesql @tsql, N'@disk NVARCHAR(255), @name NVARCHAR(128), @description NVARCHAR(255)', @disk= @backupDevice, @name= @backupName, @description= N'DBAdmin.BackupDBs';
	IF @@ERROR <> 0 OR @returnValue <> 0 SET @errors= @errors + 1;
  END TRY BEGIN CATCH;
	SET @errors= @errors + 1;
	PRINT '*** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR ***';
	PRINT CAST(ERROR_NUMBER() AS NVARCHAR(11)) + ': ' + RTRIM(ERROR_MESSAGE());
  END CATCH;

END; --WHILE
CLOSE list;
DEALLOCATE list;
DROP TABLE #backupHeader;

-- report errors
PRINT CHAR(13) + CHAR(13) + REPLICATE('#', 132);
PRINT 'Error Count: ' + CAST(@errors AS NVARCHAR(5));
IF @errors <> 0 RAISERROR('DBAdmin.BackupDBs', 16, 1);
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Back up transaction log for specified database(s) using native backup and default compression.
CREATE PROCEDURE DBAdmin.BackupTLOGs (
	@db NVARCHAR(MAX)= N'*' -- database transaction log(s) to back up
		) AS;
DECLARE
  @backupDirectory	NVARCHAR(255),
  @backupDevice		NVARCHAR(255),
  @backupName		NVARCHAR(128),
  @tsql				NVARCHAR(MAX),
  @dbid				INTEGER,
  @db_name			NVARCHAR(128),
  @full_exists		BIT,
  @type				CHAR(8),
  @ok_differential	BIT,
  @errors			SMALLINT,
  @returnValue		INTEGER;
SET NOCOUNT ON;
SET @backupDirectory= DBAdmin.GetInstancePath('BackupDirectory');
SET @errors= 0;
SET @returnValue= 0;

PRINT @backupDirectory;
DECLARE list CURSOR LOCAL FAST_FORWARD FOR SELECT l.database_id, l.database_name, full_exists= CASE WHEN drs.last_log_backup_lsn IS Null THEN 0 ELSE 1 END
	FROM DBAdmin.DBList(@db) l LEFT OUTER JOIN sys.database_recovery_status drs ON (l.database_id = drs.database_id)
	WHERE l.recovery_model IN (1,2) AND l.source_database_id IS Null AND database_guid IS NOT Null
	ORDER BY l.database_id ASC;

OPEN list;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM list INTO @dbid, @db_name, @full_exists;
  IF @@FETCH_STATUS <> 0 BREAK;
  PRINT CHAR(13)+REPLICATE('=', 132);
  PRINT CONVERT(NVARCHAR(19), CURRENT_TIMESTAMP, 120) + SPACE(1) + CAST(@dbid AS NVARCHAR(9)) + SPACE(1) + @db_name;

  -- build the statement
  IF @full_exists = 1 BEGIN
	SET @tsql= N'BACKUP LOG [' + @db_name + N'] TO DISK= @disk';
	SET @type= 'TLOG.bak';
  END;
  ELSE BEGIN
	SET @tsql= N'BACKUP DATABASE [' + @db_name + N'] TO DISK= @disk';
	SET @type= 'FULL.bak';
  END;
  SET @backupDevice= @backupDirectory + DBAdmin.NameBackupFile(@db_name, @type);
  PRINT @backupDevice;

  -- ...and do it
  PRINT N'Backing up to: ' + @backupDevice;
  BEGIN TRY;
	PRINT @tsql;
	EXECUTE @returnValue= sys.sp_executesql @tsql, N'@disk NVARCHAR(255)', @disk= @backupDevice;
	IF @@ERROR <> 0 OR @returnValue <> 0 SET @errors= @errors + 1;
  END TRY BEGIN CATCH;
	SET @errors= @errors + 1;
	PRINT '*** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR ***';
	PRINT CAST(ERROR_NUMBER() AS NVARCHAR(11)) + ': ' + RTRIM(ERROR_MESSAGE());
  END CATCH;

END;
CLOSE list;
DEALLOCATE list;

-- report errors
PRINT CHAR(13) + CHAR(13) + REPLICATE('#', 132);
PRINT 'Error Count: ' + CAST(@errors AS NVARCHAR(5));
IF @errors <> 0 RAISERROR('DBAdmin.BackupTLOGs', 16, 2);
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Execute DBCC CHECKDB on specified database(s). Continue executing after an error (not the default when run as a job
--			step). If any errors are encountered, raise a generic error at the very end.
CREATE PROCEDURE DBAdmin.CheckDBIntegrity (@db NVARCHAR(MAX) = '*') AS;
DECLARE
  @dbid		INT,
  @db_name	NVARCHAR(128),
  @errors	SMALLINT;
SET @errors= 0;
DECLARE list CURSOR LOCAL FAST_FORWARD FOR SELECT database_id, database_name FROM DBAdmin.DBList(@db) ORDER BY database_id DESC;
OPEN list;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM list INTO @dbid, @db_name;
  IF @@FETCH_STATUS <> 0 BREAK;
  PRINT CHAR(13)+REPLICATE('=', 132);
  PRINT CAST(@dbid AS NVARCHAR(13)) + SPACE(1) + @db_name;
  DBCC CHECKDB(@db_name) WITH ALL_ERRORMSGS, NO_INFOMSGS;
  IF @@ERROR <> 0 BEGIN;
	SET @errors= @errors+ 1;
	PRINT '*** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR *** ERROR ***';
	PRINT CAST(ERROR_NUMBER() AS NVARCHAR(11)) + ': ' + RTRIM(ERROR_MESSAGE());
  END;
END;
CLOSE list;
DEALLOCATE list;

-- report errors
PRINT CHAR(13) + CHAR(13) + REPLICATE('#', 132);
PRINT 'Error Count: ' + CAST(@errors AS NVARCHAR(5));
IF @errors <> 0 RAISERROR('DBAdmin.CheckDBIntegrity', 16, 3);
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Allows results of sys.dm_db_index_physical_stats to be used in a CROSS APPLY.
CREATE FUNCTION DBAdmin.dm_db_index_physical_stats (@database_id SMALLINT, @object_id INT, @index_id INT, @partition_number INT, @mode NVARCHAR(128)) 
RETURNS @dm_db_index_physical_stats TABLE ( 
	database_id					SMALLINT	NOT Null,
	object_id						INT			NOT Null,
	index_id						INT			NOT Null,
	partition_number				INT			NOT Null,
	index_depth					TINYINT		NOT Null,
	avg_fragmentation_in_percent	FLOAT			Null,
	fragment_count				BIGINT			Null,
	page_count					BIGINT		NOT Null)
BEGIN INSERT INTO @dm_db_index_physical_stats SELECT
	database_id,object_id,index_id,partition_number,index_depth,avg_fragmentation_in_percent,fragment_count,page_count
  FROM sys.dm_db_index_physical_stats (@database_id, @object_id, @index_id, @partition_number, @mode);
RETURN; END;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	
CREATE PROCEDURE DBAdmin.DefragmentIndexes (@db NVARCHAR(MAX)= N'*', @mode NVARCHAR(128)= N'LIMITED' /* LIMITED, SAMPLED, DETAILED */, @timelimit INT= 2147483647 /* minutes */) AS;
CREATE TABLE #dm_db_index_physical_stats (
  database_id		SMALLINT	NOT Null,
  object_id			INT			NOT Null,
  index_id			INT			NOT Null,
  partition_number	INT			NOT Null,
  fragment_count	BIGINT		NOT Null,
  fragment_percent	FLOAT		NOT Null,
  do_rebuild		BIT			NOT Null DEFAULT (0),
  tsql_command		NVARCHAR(MAX)	Null);
DECLARE
  @database_id		INT,
  @timestart		DATETIME,
  @database_name	NVARCHAR(128),
  @tsql_command		NVARCHAR(MAX);
SET @database_id= DB_ID(@db);
IF @timelimit IS Null OR @timelimit <= 0 SET @timelimit= 2147483647;
SET NOCOUNT ON;

-- gather index statistics
PRINT N'Gather ' + @mode + N' Index Statistics';
INSERT INTO #dm_db_index_physical_stats (database_id, object_id, index_id, partition_number, fragment_count, fragment_percent) SELECT
	l.database_id, s.object_id, s.index_id, s.partition_number, s.fragment_count, s.avg_fragmentation_in_percent
  FROM DBAdmin.DBList(@db) l CROSS APPLY DBAdmin.dm_db_index_physical_stats(l.database_id, Null, Null, Null, @mode) s
  WHERE l.is_read_only = 0 AND 0 < s.index_id AND 8 < s.page_count AND 1 < s.index_depth AND 20.0 <= s.avg_fragmentation_in_percent;

-- fetch additional index info from each DB in turn
PRINT N'Fetch Additional Index Info';
DECLARE dblist CURSOR LOCAL FAST_FORWARD FOR WITH d AS (SELECT DISTINCT database_name= DB_NAME(database_id), database_id FROM #dm_db_index_physical_stats) SELECT database_name FROM d ORDER BY database_id DESC;
OPEN dblist;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM dblist INTO @database_name;
  IF @@FETCH_STATUS <> 0 BREAK;
  PRINT NCHAR(9) + @database_name;
  SET @tsql_command= N'USE [' + @database_name + '];
	WITH p AS (SELECT [object_id], [index_id], [partition_count]= COUNT([partition_id]) FROM sys.partitions GROUP BY [object_id], [index_id])
	UPDATE #dm_db_index_physical_stats SET
		partition_number= CASE p.partition_count WHEN 1 THEN 0 ELSE partition_number END,
		do_rebuild		= CASE WHEN 33.3 <= fragment_percent OR INDEXPROPERTY(i.[object_id], i.Name, N''IsPageLockDisallowed'') = 1 THEN 1 ELSE 0 END,
		tsql_command	= N''IF OBJECT_NAME('' + CAST(s.[object_id] AS NVARCHAR(10)) + '','' + CAST(database_id AS NVARCHAR(5)) + '') IS NOT Null ALTER INDEX ['' + i.name + ''] ON ['' + DB_NAME() + ''].['' + OBJECT_SCHEMA_NAME(s.[object_id],s.database_id) + ''].['' + OBJECT_NAME(s.[object_id],database_id) + '']''
	  FROM #dm_db_index_physical_stats s
		INNER JOIN sys.indexes i ON (s.[object_id] = i.[object_id] AND s.[index_id] = i.[index_id] AND s.database_id = DB_ID())
		INNER JOIN             p ON (i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]);';
  EXECUTE (@tsql_command);
END;
CLOSE dblist;
DEALLOCATE dblist;

-- Roll Call
SELECT [Roll Call]= NCHAR(9) + DB_NAME(ips.database_id) + N'.' + OBJECT_SCHEMA_NAME(ips.object_id, ips.database_id) + N'.' + OBJECT_NAME(ips.object_id, ips.database_id) + N':' + CAST(index_id AS NVARCHAR(10)) + N' (' + CAST(ips.fragment_count AS NVARCHAR(10)) + N')' FROM #dm_db_index_physical_stats ips ORDER BY ips.fragment_count DESC;

-- build the T-SQL
PRINT N'Building T-SQL';
UPDATE #dm_db_index_physical_stats SET tsql_command= tsql_command+ N' REORGANIZE' + CASE partition_number WHEN 0 THEN N'' ELSE N' PARTITION=' + CAST(partition_number AS NVARCHAR(5)) END + N';'
  WHERE do_rebuild = 0;
UPDATE #dm_db_index_physical_stats SET tsql_command= tsql_command+ N' REBUILD'    + CASE partition_number WHEN 0 THEN N'' ELSE N' PARTITION=' + CAST(partition_number AS NVARCHAR(5)) END /*+ ' WITH (SORT_IN_TEMPDB=ON)'*/ + N';'
  WHERE do_rebuild = 1;

-- do it
PRINT N'Clock Is Running';
SET @timestart= CURRENT_TIMESTAMP;
DECLARE list CURSOR LOCAL FAST_FORWARD FOR SELECT tsql_command FROM #dm_db_index_physical_stats ORDER BY fragment_count DESC;
OPEN list;
WHILE DATEDIFF(mi, @timestart, CURRENT_TIMESTAMP) < @timelimit BEGIN;
  FETCH NEXT FROM list INTO @tsql_command;
  IF @@FETCH_STATUS <> 0 BREAK;
  EXECUTE (@tsql_command);
END;
CLOSE list;
DEALLOCATE list;

DROP TABLE #dm_db_index_physical_stats;
PRINT N'COMPLETE';
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Generate T-SQL to re-create the server's logins and server role membership. Returns multiple rowsets; best used when output is
--			directed to a file. Maximum line length is 4000 characters.
CREATE PROCEDURE DBAdmin.ScriptLogins AS;
SET NOCOUNT ON;
PRINT '/***********************************************************************************************************************************';
PRINT @@SERVERNAME;
PRINT CURRENT_TIMESTAMP;
PRINT '*/--';

-- Active Directory Logins
SELECT [-- Active Directory Logins]= 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ''' + name + ''')
	CREATE LOGIN [' + name + '] FROM WINDOWS WITH DEFAULT_DATABASE = [' + default_database_name + '], DEFAULT_LANGUAGE = ['+default_language_name + '];	-- ' + type_desc COLLATE DATABASE_DEFAULT
  FROM sys.server_principals
  WHERE type IN ('G', 'U') AND name NOT LIKE 'BUILTIN%' AND name NOT LIKE 'NT AUTHORITY%' AND name NOT LIKE '%\SQLServer%' AND name NOT LIKE 'NT SERVICE\%'
  ORDER BY type_desc, name;

-- SQL Server Logins
SELECT [-- SQL Server Logins]= 'IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = ''' + name + ''')
	CREATE LOGIN [' + name + '] WITH PASSWORD = ' + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID = ' + sys.fn_varbintohexstr(sid) + ', DEFAULT_DATABASE = [' + default_database_name + '], DEFAULT_LANGUAGE = [' + default_language_name + '], CHECK_EXPIRATION = ' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY = ' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END + ';'
  FROM sys.sql_logins
  WHERE name NOT IN ('sa', 'guest') AND name NOT LIKE '##%##'
  ORDER BY name;

-- Disabled Logins
SELECT [-- Disabled Logins]= 'ALTER LOGIN [' + name + '] DISABLE;' 
  FROM sys.server_principals 
  WHERE is_disabled = 1 AND name NOT LIKE '##%##'
  ORDER BY name;

-- Role Membership
SELECT [-- Role Membership]= 'EXECUTE dbo.sp_addsrvrolemember @loginame = ''' + l.name + ''', @rolename = ''' + r.name + ''';'
  FROM sys.server_principals r
	INNER JOIN sys.server_role_members rm ON (r.principal_id = rm.role_principal_id)
	RIGHT JOIN sys.server_principals l ON (rm. member_principal_id = l.principal_id)
  WHERE l.type <> 'R' AND r.name IS NOT Null
	AND l.name NOT IN ('sa') AND l.name NOT LIKE 'BUILTIN%' and l.name NOT LIKE 'NT AUTHORITY%' and l.name NOT LIKE '%\SQLServer%'
  ORDER BY l.name;

SET NOCOUNT OFF;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Delete history and log items (except for SQL Server logs) older than the specified date. Defaults to 13 weeks back.
CREATE PROCEDURE DBAdmin.PurgeHistory (@oldest_date DATETIME =Null) AS;
IF @oldest_date IS Null SET @oldest_date= DATEADD(wk, -13, CURRENT_TIMESTAMP);

PRINT N'sp_maintplan_delete_log';		EXECUTE msdb.dbo.sp_maintplan_delete_log		@oldest_time  = @oldest_date;
PRINT N'sysmail_delete_mailitems_sp';	EXECUTE msdb.dbo.sysmail_delete_mailitems_sp	@sent_before  = @oldest_date;
PRINT N'sysmail_delete_log_sp';			EXECUTE msdb.dbo.sysmail_delete_log_sp			@logged_before= @oldest_date;
PRINT N'sp_purge_jobhistory';			EXECUTE msdb.dbo.sp_purge_jobhistory			@oldest_date  = @oldest_date;

PRINT N'sp_delete_backuphistory';
WHILE 0 < (SELECT COUNT(command) FROM sys.dm_exec_requests WHERE command LIKE 'BACKUP%') WAITFOR DELAY '00:01:00';	-- wait a minute
EXECUTE msdb.dbo.sp_delete_backuphistory @oldest_date= @oldest_date;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	
CREATE FUNCTION DBAdmin.BuildRestorePlan (@database_name NVARCHAR(128), @stopat DATETIME = Null) RETURNS @RestorePlan TABLE (backup_finish_date DATETIME NOT Null, backup_set_id INT NOT Null, media_set_id INT NOT Null, type CHAR(1) NOT Null DEFAULT 'L', physical_device_name NVARCHAR(260) NOT Null) AS BEGIN
 DECLARE @logs TABLE (
	backup_set_id		INT,
	media_set_id		INT,
	first_lsn			NUMERIC(25,0),
	last_lsn			NUMERIC(25,0),
	backup_finish_date	DATETIME,
	prior_finish_date	DATETIME);
DECLARE
  @database_guid		UNIQUEIDENTIFIER,
  @database_backup_lsn	NUMERIC(25,0),
  @last_lsn				NUMERIC(25,0),
  @backup_finish_date	DATETIME,
  @backup_set_id		INT,
  @media_set_id			INT,
  @physical_device_name	NVARCHAR(260);
IF @stopat IS Null SET @stopat= CURRENT_TIMESTAMP;


-- fetch latest FULL (D) which is earlier than the RPO
WITH d AS (
	SELECT backup_finish_date, backup_set_id, media_set_id, first_lsn, last_lsn, database_guid, ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_start_date DESC) AS position
	  FROM msdb.dbo.backupset
	  WHERE is_copy_only = 0 AND type = 'D' AND database_name = @database_name AND backup_finish_date <= @stopat  )
  SELECT
	@database_guid			= d.database_guid,
	@database_backup_lsn	= d.first_lsn,
	@last_lsn				= d.last_lsn,
	@backup_finish_date		= d.backup_finish_date,
	@backup_set_id			= d.backup_set_id,
	@media_set_id			= d.media_set_id,
	@physical_device_name	= m.physical_device_name
  FROM d INNER JOIN msdb.dbo.backupmediafamily m ON (d.media_set_id = m.media_set_id) WHERE d.position = 1;
IF @@ROWCOUNT = 1 INSERT INTO @RestorePlan (backup_finish_date, backup_set_id, media_set_id, type, physical_device_name) VALUES (@backup_finish_date, @backup_set_id, @media_set_id, 'D', @physical_device_name);
ELSE GOTO ENDSCRIPT;

-- fetch latest DIFF (I) which is earlier than the RPO and downstream of the FULL
WITH i AS (
	SELECT backup_finish_date, backup_set_id, media_set_id, last_lsn, ROW_NUMBER() OVER (PARTITION BY database_guid, database_backup_lsn ORDER BY backup_finish_date DESC) AS position
	  FROM msdb.dbo.backupset
	  WHERE is_copy_only = 0 AND type = 'I' AND database_guid = @database_guid AND database_backup_lsn = @database_backup_lsn AND backup_finish_date <= @stopat)
  SELECT
	@last_lsn				= COALESCE(i.last_lsn,@last_lsn),
	@backup_finish_date		= i.backup_finish_date,
	@backup_set_id			= i.backup_set_id,
	@media_set_id			= i.media_set_id,
	@physical_device_name	= m.physical_device_name
 FROM i INNER JOIN msdb.dbo.backupmediafamily m ON (i.media_set_id = m.media_set_id) WHERE position = 1;
IF @@ROWCOUNT = 1 INSERT INTO @RestorePlan (backup_finish_date, backup_set_id, media_set_id, type, physical_device_name) VALUES (@backup_finish_date, @backup_set_id, @media_set_id, 'I', @physical_device_name);

-- gather TLOGs (L) which are earlier than the RPO and downstream of the FULL/DIFF...
WITH l AS (
SELECT backup_set_id, media_set_id, first_lsn, last_lsn, backup_finish_date, ROW_NUMBER() OVER (ORDER BY backup_start_date) AS position
  FROM msdb.dbo.backupset
  WHERE is_copy_only = 0 AND type = 'L' AND first_lsn < last_lsn AND database_guid = @database_guid AND database_backup_lsn = @database_backup_lsn	)
INSERT INTO @logs
SELECT l.backup_set_id, l.media_set_id, l.first_lsn, l.last_lsn, l.backup_finish_date, lP.backup_finish_date AS prior_finish_date
  FROM l LEFT JOIN l lP ON (l.position = lP.position+1)
  WHERE lP.backup_finish_date IS Null OR lP.backup_finish_date <= @stopat
  ORDER BY l.backup_finish_date;
-- ...then verify we have a valid backup chain and fetch the physical_device_name
WITH l AS (
	SELECT backup_set_id, media_set_id, first_lsn, last_lsn, backup_finish_date, prior_finish_date
	  FROM @logs
	    WHERE @last_lsn+1 BETWEEN first_lsn AND last_lsn
	UNION ALL SELECT lN.backup_set_id, lN.media_set_id, lN.first_lsn, lN.last_lsn, lN.backup_finish_date, lN.prior_finish_date
	  FROM l INNER JOIN @logs lN ON (l.last_lsn = lN.first_lsn)
	  )
  INSERT INTO @RestorePlan (backup_finish_date, backup_set_id, media_set_id, physical_device_name) SELECT  l.backup_finish_date, l.backup_set_id, l.media_set_id, m.physical_device_name 
	FROM l INNER JOIN msdb.dbo.backupmediafamily m ON (l.media_set_id = m.media_set_id)
	ORDER BY last_lsn
	OPTION (MAXRECURSION 0);

ENDSCRIPT:
RETURN;END;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	
CREATE FUNCTION DBAdmin.BuildRestoreTSQL (@database_name NVARCHAR(128), @stopat DATETIME= Null, @restore_name NVARCHAR(128)= Null) RETURNS @RestoreTSQL TABLE (ordinal INT NOT Null IDENTITY, tsql_command NVARCHAR(MAX) NOT Null) AS BEGIN
DECLARE @RestorePlan TABLE (backup_finish_date DATETIME NOT Null, backup_set_id INT NOT Null, media_set_id INT NOT Null, type CHAR(1) NOT Null DEFAULT 'L', physical_device_name NVARCHAR(260) NOT Null);
DECLARE
  @backup_set_id		INT,
  @DefaultData			NVARCHAR(255),
  @DefaultLog			NVARCHAR(255),
  @DefaulFTData			NVARCHAR(255),
  @tsql_command			NVARCHAR(MAX);
SET @DefaultData = master.DBAdmin.GetInstancePath('DefaultData');
SET @DefaultLog  = master.DBAdmin.GetInstancePath('DefaultLog');
SET @DefaulFTData= master.DBAdmin.GetInstancePath('SQLDataRoot') + N'FTData\';

IF @stopat IS Null SET @stopat= CURRENT_TIMESTAMP;
IF @restore_name IS Null SET @restore_name= @database_name;

INSERT INTO	@RestorePlan SELECT backup_finish_date, backup_set_id, media_set_id, type, physical_device_name FROM DBAdmin.BuildRestorePlan(@database_name, @stopat) ORDER BY backup_finish_date;

-- Full
SELECT @backup_set_id= backup_set_id, @tsql_command= N'RESTORE DATABASE [' + @restore_name + '] FROM DISK= N''' + physical_device_name + N''' WITH STATS= 05, NORECOVERY' FROM @RestorePlan WHERE type = 'D';

-- Data Files
SELECT @tsql_command= @tsql_command + CHAR(13) + N', MOVE N''' + logical_name + ''' TO N''' + @DefaultData + RIGHT(physical_name, CHARINDEX(N'\', REVERSE(physical_name))-1) + ''''
 FROM msdb.dbo.backupfile b WHERE backup_set_id = @backup_set_id AND file_type = 'D';

-- Full-Text Catalogs
SELECT @tsql_command= @tsql_command + CHAR(13) + N', MOVE N''' + logical_name + ''' TO N''' + @DefaulFTData + RIGHT(physical_name, CHARINDEX(N'\', REVERSE(physical_name))-1) + ''''
 FROM msdb.dbo.backupfile b WHERE backup_set_id = @backup_set_id AND file_type = 'F';

-- Log Files
SELECT @tsql_command= @tsql_command + CHAR(13) + N', MOVE N''' + logical_name + ''' TO N''' + @DefaultLog + RIGHT(physical_name, CHARINDEX(N'\', REVERSE(physical_name))-1) + ''';'
 FROM msdb.dbo.backupfile b WHERE backup_set_id = @backup_set_id AND file_type = 'L';

INSERT INTO @RestoreTSQL (tsql_command) VALUES (@tsql_command);

-- Differential
INSERT INTO @RestoreTSQL (tsql_command) SELECT tsql_command= N'RESTORE DATABASE [' + @restore_name + '] FROM DISK= N''' + physical_device_name + N''' WITH STATS= 10, NORECOVERY;' FROM @RestorePlan WHERE type = 'I';

-- Transaction Logs
INSERT INTO @RestoreTSQL (tsql_command) SELECT tsql_command= N'RESTORE LOG      [' + @restore_name + '] FROM DISK= N''' + physical_device_name + N''' WITH STATS= 20, NORECOVERY' + CASE WHEN @stopat < backup_finish_date THEN ', STOPAT= ''' + CONVERT(NVARCHAR(23), @stopat, 121) + ''';' ELSE ';' END FROM @RestorePlan WHERE type = 'L';

RETURN;END;
GO


--================================================================================================================================--
-- Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
--  Usage:
--	CREATE TABLE #BackupFolder (BackupFile NVARCHAR(260) NOT Null PRIMARY KEY CLUSTERED);
--	INSERT INTO #BackupFolder {filelist};
--	EXECUTE DBAdmin.GetDBBackupPurgeList;
CREATE PROCEDURE DBAdmin.GetDBBackupPurgeList (@db NVARCHAR(MAX)= N'*', @retention SMALLINT= 72) AS
DECLARE @retain TABLE (physical_device_name NVARCHAR(260) NOT Null);
DECLARE list CURSOR LOCAL FAST_FORWARD FOR SELECT database_name FROM DBAdmin.DBList(@db);
DECLARE
  @database_name	NVARCHAR(128),
  @recovery_point	DATETIME;

IF OBJECT_ID('TempDB..#BackupFolder') IS Null BEGIN;
  SELECT physical_device_name= CAST(Null AS NVARCHAR(255));
  RETURN;
END;

SET @retention= CASE WHEN @retention IS Null THEN 72 ELSE ABS(@retention) END;
SET @recovery_point= DATEADD(hh,-@retention,GETDATE());

OPEN list;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM list INTO @database_name;
  IF @@FETCH_STATUS <> 0 BREAK;
  INSERT INTO @retain SELECT m.physical_device_name
	  FROM DBAdmin.BuildRestorePlan(@database_name, @recovery_point) rp
		INNER JOIN msdb.dbo.backupset b ON (rp.backup_set_id = b.backup_set_id)
		INNER JOIN msdb.dbo.backupmediafamily m ON (b.media_set_id = m.media_set_id)
	UNION
	SELECT m.physical_device_name
		FROM msdb.dbo.backupset b INNER JOIN msdb.dbo.backupmediafamily m ON (b.media_set_id = m.media_set_id)
		WHERE b.is_copy_only = 0 AND b.database_name = @database_name AND @recovery_point <= b.backup_finish_date;
END;
CLOSE list;
DEALLOCATE list;

SELECT m.physical_device_name
  FROM DBAdmin.DBList(@db) dl
	INNER JOIN msdb.dbo.backupset b ON (dl.database_name = b.database_name)
	INNER JOIN msdb.dbo.backupmediafamily m ON (b.media_set_id = m.media_set_id)
	INNER JOIN #BackupFolder bf ON (m.physical_device_name = bf.BackupFile)
  WHERE m.device_type = 2
  EXCEPT SELECT physical_device_name FROM @retain
ORDER BY physical_device_name;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Returns the server default trace as a rowset.
CREATE VIEW DBAdmin.ShowDefaultTrace AS SELECT
	t.TextData,				-- NVARCHAR(MAX)
	t.BinaryData,			-- VARBINARY(MAX)
	t.DatabaseID,			-- INT
	t.TransactionID,		-- BIGINT
	t.LineNumber,			-- INT
	t.NTUserName,			-- NVARCHAR(256)
	t.NTDomainName,			-- NVARCHAR(256)
	t.HostName,				-- NVARCHAR(256)
	t.ClientProcessID,		-- INT
	t.ApplicationName,		-- NVARCHAR(256)
	t.LoginName,			-- NVARCHAR(256)
	t.SPID,					-- INT
	t.Duration,				-- BIGINT
	t.StartTime,			-- DATETIME
	t.EndTime,				-- DATETIME
	t.Reads,				-- BIGINT
	t.Writes,				-- BIGINT
	t.CPU,					-- INT
	t.Permissions,			-- BIGINT
	t.Severity,				-- INT
	t.EventSubClass,		-- INT
	v.SubClass_Name,
	t.ObjectID,				-- INT
	t.Success,				-- INT
	t.IndexID,				-- INT
	t.IntegerData,			-- INT
	t.ServerName,			-- NVARCHAR(256)
	t.EventClass,			-- INT
	e.Name AS EventClass_Name,
	c.Name AS EventClass_Category,
	t.ObjectType,			-- INT
	t.NestLevel,			-- INT
	t.State,				-- INT
	t.Error,				-- INT
	t.Mode,					-- INT
	t.Handle,				-- INT
	t.ObjectName,			-- NVARCHAR(256)
	t.DatabaseName,			-- NVARCHAR(256)
	t.FileName,				-- NVARCHAR(256)
	t.OwnerName,			-- NVARCHAR(256)
	t.RoleName,				-- NVARCHAR(256)
	t.TargetUserName,		-- NVARCHAR(256)
	t.DBUserName,			-- NVARCHAR(256)
	t.LoginSID,				-- VARBINARY(MAX)
	t.TargetLoginName,		-- NVARCHAR(256)
	t.TargetLoginSID,		-- VARBINARY(MAX)
	t.ColumnPermissions,	-- INT
	t.LinkedServerName,		-- NVARCHAR(256)
	t.ProviderName,			-- NVARCHAR(256)
	t.MethodName,			-- NVARCHAR(256)
	t.RowCounts,			-- BIGINT
	t.RequestID,			-- INT
	t.XactSequence,			-- BIGINT
	t.EventSequence,		-- BIGINT
	t.BigintData1,			-- BIGINT
	t.BigintData2,			-- BIGINT
	t.GUID,					-- UNIQUEIDENTIFIER
	t.IntegerData2,			-- INT
	t.ObjectID2,			-- BIGINT
	t.Type,					-- INT
	t.OwnerID,				-- INT
	t.ParentName,			-- NVARCHAR(256)
	t.IsSystem,				-- INT
	t.Offset,				-- INT
	t.SourceDatabaseID,		-- INT
	t.SQLHandle,			-- VARBINARY(MAX)
	t.SessionLoginName,		-- NVARCHAR(256)
	t.PlanHandle			-- VARBINARY(MAX)
--	t.[GroupID]				-- INT
  FROM sys.fn_trace_gettable(master.DBAdmin.GetInstancePath(N'ErrorDumpDir')+N'log.trc', default) t
	INNER JOIN sys.trace_events e on (t.EventClass = e.Trace_Event_ID)
	INNER JOIN sys.trace_categories c ON (e.Category_ID = c.Category_ID)
	LEFT JOIN sys.trace_subclass_values v ON (t.EventSubClass = v.SubClass_Value AND e.Trace_Event_ID = v.Trace_Event_ID);
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Collect wait statistics.
--	CREATE VIEW DBAdmin.ShowWaitStats
DECLARE @tsql_command NVARCHAR(MAX);
SET @tsql_command= N'CREATE VIEW DBAdmin.ShowWaitStats AS SELECT
		server_name	= CAST(SERVERPROPERTY(N''ServerName'') AS NVARCHAR(128)),
		server_time	= CAST(SYSDATETIMEOFFSET() AS DATETIMEOFFSET(0)),
		wait_type,
		waiting_tasks_count,
		wait_time_ms,
		max_wait_time_ms,
		signal_wait_time_ms
	  FROM sys.dm_os_wait_stats
	  WHERE 0 < waiting_tasks_count AND 0 < wait_time_ms AND wait_type NOT IN (N''DISPATCHER_QUEUE_SEMAPHORE'', N''WAITFOR'');';
IF DBAdmin.ProductVersion() < 10.0 SET @tsql_command= REPLACE(@tsql_command, N'CAST(SYSDATETIMEOFFSET() AS DATETIMEOFFSET(0))', N'CURRENT_TIMESTAMP');
EXECUTE (@tsql_command);
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Return currently waiting tasks.
CREATE VIEW DBAdmin.ShowWaitingTasks AS
WITH wt (server_name, server_time, session_id, exec_context_id, blocking_session_id, blocking_exec_context_id, wait_duration_ms, wait_type)
	AS (SELECT CAST(SERVERPROPERTY(N'ServerName') AS NVARCHAR(128)), CURRENT_TIMESTAMP, session_id, exec_context_id, blocking_session_id, blocking_exec_context_id, wait_duration_ms, wait_type
		FROM sys.dm_os_waiting_tasks WHERE 50 < session_id OR session_id IN (SELECT DISTINCT blocking_session_id FROM sys.dm_os_waiting_tasks))
SELECT	-- Waiters
	wt.server_name,
	wt.server_time,
	wt.session_id,
	wt.exec_context_id,
	wt.blocking_session_id,
	wt.blocking_exec_context_id,
	wt.wait_type,
	wt.wait_duration_ms,
	dbname					= DB_NAME(s.dbid),
	host_name				= s.hostname,
	s.program_name,
	login_name				= RTRIM(s.loginame),
	r.open_transaction_count,
	r.command,
	st.text
  FROM wt
	INNER JOIN  sys.sysprocesses					s ON (wt.session_id = s.spid AND wt.exec_context_id = s.ecid) 
	INNER JOIN  sys.dm_exec_requests				r ON (wt.session_id = r.session_id)
	OUTER APPLY sys.dm_exec_sql_text(r.sql_handle)	st
UNION ALL
SELECT	-- Blockers
	wt.server_name,
	wt.server_time,
	session_id				= s.spid,
	exec_context_id			= s.ecid,
	blocking_session_id		= Null,
	blocking_exec_context_id= Null,
	wait_type				= Null,
	wait_duration_ms		= Null,
	dbname					= DB_NAME(s.dbid),
	host_name				= RTRIM(s.hostname),
	program_name			= RTRIM(s.program_name),
	login_name				= RTRIM(s.loginame),
	r.open_transaction_count,
	r.command,
	st.text
  FROM wt
	INNER JOIN  sys.sysprocesses					s ON (wt.blocking_session_id = s.spid AND ISNull(wt.blocking_exec_context_id,0) = ISNull(s.ecid,0)) 
	LEFT JOIN   sys.dm_exec_requests				r ON (wt.blocking_session_id = r.session_id)
	OUTER APPLY sys.dm_exec_sql_text(r.sql_handle)	st;
GO



--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Kill all connections in a specific database. Useful immediately before performing a restore over an existing database.
CREATE PROCEDURE DBAdmin.KillDBConnections (@db_name NVARCHAR(128)) AS;
DECLARE
  @dbid			INT,
  @victims		INT,
  @tsql_command	NVARCHAR(MAX);

SET @dbid= DB_ID(@db_name);
SET @victims= 0;
SET @tsql_command= N'';

IF @dbid IS Null PRINT 'Database [' + @db_name + '] does not exist.';
ELSE BEGIN;
  WITH v (spid) AS (SELECT DISTINCT spid FROM dbo.sysprocesses WHERE dbid = @dbid)
	SELECT  @tsql_command= @tsql_command+ 'KILL ' + CAST(spid AS VARCHAR(6)) + ';' FROM v;
  SET @victims= @@ROWCOUNT;
  EXECUTE (@tsql_command);
  PRINT 'Killed ' + CAST(@victims AS NVARCHAR(6)) + ' processes in [' + @db_name + '].';
END;
GO


--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Show buffer pool size and utilization.
CREATE VIEW DBAdmin.ShowBufferPool AS
WITH dobd (database_id, buffer_pool_mb, free_space_in_mb) AS (
	SELECT database_id, COUNT(*)/128.0, SUM(free_space_in_bytes/1048576.0)
	  FROM sys.dm_os_buffer_descriptors
	  GROUP BY database_id)
SELECT
	[Database Name]		= CASE database_id WHEN 32767 THEN N'ResourceDB' ELSE DB_NAME(database_id) END,
	[Buffer Pool (MB)]	= CAST(buffer_pool_mb AS DECIMAL(8,1)),
	[Utilization %]		= CAST((1-(free_space_in_mb/buffer_pool_mb))*100.0 AS DECIMAL(5,1))
  FROM dobd UNION ALL
SELECT
	[Database Name]		= @@SERVERNAME,
	[Buffer Pool (MB)]	= CAST(SUM(buffer_pool_mb)AS DECIMAL(8,1)),
	[Utilization %]		= CAST(100.0*(1-(SUM(free_space_in_mb)/SUM(buffer_pool_mb))) AS DECIMAL(5,1))
  FROM dobd;
GO


--================================================================================================================================--
-- Share the Love
GRANT SELECT ON DBAdmin.ShowBackupsStatus TO PUBLIC;
SET NOCOUNT OFF;
PRINT N'COMPLETE';
GO